﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace text1
{
    public partial class fjxx : Form
    {
        //数据库连接字符串
        string constr = "server=LAPTOP-NHBSREVG;database=社区数据库;uid=sa;pwd=990823";
        public fjxx()
        {
            InitializeComponent();
        }

        private void Stu_Load(object sender, EventArgs e)
        {

        }

        private void find_fangzhu_Click(object sender, EventArgs e)
        {
          

           //获得文本框的值
            String text1 = textBox1.Text.Trim();
            String text2 = textBox2.Text.Trim();
            String text3 = textBox3.Text.Trim();

            //参数
            SqlParameter para_text1;
            SqlParameter para_text2;
            SqlParameter para_text3;

            //创建连接并打开
            SqlConnection cnn = new SqlConnection(constr);
            cnn.Open();

            //创建查询语句
             String find = "select 房间.房间号,户型,面积,住户人数,房间.房主身份证号,房主.姓名 " +
                 "from 房主,房间 "
                 +"where 房主.房主身份证号=房间.房主身份证号 ";
           /* String find = "select 房间.房间号,面积,住户人数,房间.房主身份证号,房主.姓名,住户.住户身份证号,住户.姓名 " +
            "from 房主,房间,住户,住户居住 "
            + "where 房主.房主身份证号=房间.房主身份证号 and 住户居住.房间编号=房间.房间号 and 住户.住户身份证号=住户居住.住户身份证号 and 住户.是否户主='是'  ";*/

            //根据textbox的值是否为空，进行进一步的判断，将sql语句补充完整
            if (text1.Length!=0)
            {
                //find += "and 房间.房间号= '"+text1+"'";
                find += "and 房间.房间号=@text1 ";
            }
            if (text2.Length!=0)
            {
                //find += "and 房间.房主身份证号='" + text2 + "'";
                find += "and 房间.房主身份证号=@text2 ";
            }
            if (text3.Length!=0)
            {
                //find += "and 房主.姓名= '" + text3+"'";
                find +=" and 房主.姓名=@text3 ";
            }

            //创建sqlDataAdapter
            SqlDataAdapter myDa = new SqlDataAdapter(find, cnn);
            if(text1.Length!=0)
            {
                para_text1 = new SqlParameter("@text1",text1);
                myDa.SelectCommand.Parameters.Add(para_text1);
            }
            if (text2.Length != 0)
            {
                para_text2 = new SqlParameter("@text2", text2);
                myDa.SelectCommand.Parameters.Add(para_text2);
            }
            if (text3.Length != 0)
            {
                para_text3 = new SqlParameter("@text3", text3);
                myDa.SelectCommand.Parameters.Add(para_text3);
            }

            //创建DataSet用来储存数据
            DataSet DS = new DataSet();
            //将数据填充到DataSet中
            myDa.Fill(DS,"performtable");
            dataGridView1.DataSource = DS.Tables["performtable"];
            //关闭连接
            cnn.Close();

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void textBox3_TextChanged(object sender, EventArgs e)
        {

        }

        private void add_Click(object sender, EventArgs e)
        {
            //获得文本框的值
            String text1 = textBox1.Text.Trim();
            String text2 = textBox2.Text.Trim();
            String text3 = textBox3.Text.Trim();


            //创建连接并打开
            SqlConnection cnn = new SqlConnection(constr);
            cnn.Open();
            main nf = new main();
            nf.ShowDialog();
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }
    }
}
